#!/bin/bash
#声明当前脚本使用bash解释器质性
#接收外部传入的日期参数，如果外部没有传入日期参数，怎么办？
#严格:外部没有传入，就退出 exit  非严格：如果外部没有传入，自己生成一个默认的日期(当前机器时间的昨天)
#-n代表判断字符串的长度，如果字符串长度为0，返回false,否则返回true
if [ -n "$1" ]
then
	do_date=$1
else
	#echo 必须给我传一个日期
	#exit
	do_date= $(date -d yesterday +%F)
fi

echo 当前要操作的日期是$do_date
dim_user_zip="
    set hive.exec.dynamic.partition.mode=nonstrict;
with new as (
    select
        id,
        login_name,
        nick_name,
        passwd,
        real_name,
        phone_num,
        email,
        head_img,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        status,
        '2022-07-15' start_date,
        '9999-12-31' end_date
    from (
         select
            data.id id,
            data.login_name login_name,
            data.nick_name nick_name,
            md5(data.passwd) passwd,
            md5(data.real_name) real_name,
            md5(data.phone_num) phone_num,
            md5(data.email) email,
            data.head_img head_img,
            data.user_level user_level,
            data.birthday birthday,
            data.gender gender,
            data.create_time create_time,
            data.operate_time operate_time,
            data.status status,
            row_number() over (partition by data.id order by ts desc) rn
    from ods_user_info_inc where dt='2022-07-05'
             )t1 where rn=1
),old as (
    select
        id,
       login_name,
       nick_name,
       passwd,
       real_name,
       phone_num,
       email,
       head_img,
       user_level,
       birthday,
       gender,
       create_time,
       operate_time,
       status,
       start_date,
       end_date,
        dt
    from dim_user_zip where dt='9999-12-31'
), user_full as (
    select
        new.id new_id,
        new.login_name new_login_name,
        new.nick_name new_nick_name,
        new.passwd new_passwd,
        new.real_name new_real_name,
        new.phone_num new_phone_num,
        new.email new_email,
        new.head_img new_head_img,
        new.user_level new_user_level,
        new.birthday new_birthday,
        new.gender new_gender,
        new.create_time new_create_time,
        new.operate_time new_operate_time,
        new.status new_status,
        new.start_date new_start_date,
        new.end_date new_end_date,
        old.id old_id,
        old.login_name old_login_name,
        old.nick_name old_nick_name,
        old.passwd old_passwd,
        old.real_name old_real_name,
        old.phone_num old_phone_num,
        old.email old_email,
        old.head_img old_head_img,
        old.user_level old_user_level,
        old.birthday old_birthday,
        old.gender old_gender,
        old.create_time old_create_time,
        old.operate_time old_operate_time,
        old.status old_status,
        old.start_date old_start_date,
        old.end_date old_end_date

    from new full join old
    on new.id= old.id
)
insert overwrite table dim_user_zip partition (dt)
--找到最新数据
select
    if(new_id is not null,new_id,old_id),
    if(new_id is not null,new_login_name,old_login_name),
    if(new_id is not null,new_nick_name,old_nick_name),
    if(new_id is not null,new_passwd,old_passwd),
    if(new_id is not null,new_real_name,old_real_name),
    if(new_id is not null,new_phone_num,old_phone_num),
    if(new_id is not null,new_email,old_email),
    if(new_id is not null,new_head_img,old_head_img),
    if(new_id is not null,new_user_level,old_user_level),
    if(new_id is not null,new_birthday,old_birthday),
    if(new_id is not null,new_gender,old_gender),
    if(new_id is not null,new_create_time,old_create_time),
    if(new_id is not null,new_operate_time,old_operate_time),
    if(new_id is not null,new_status,old_status),
    if(new_id is not null,new_start_date,old_start_date),
    if(new_id is not null,new_end_date,old_end_date),
       '9999-12-31' dt
from user_full
union
--找到失效的数据
select
    old_id,
    old_login_name,
    old_nick_name,
    old_passwd,
    old_real_name,
    old_phone_num,
    old_email,
    old_head_img,
    old_user_level,
    old_birthday,
    old_gender,
    old_create_time,
    old_operate_time,
    old_status,
    old_start_date,
    cast(date_sub('2022-07-05',1) as string),
       cast(date_sub('2022-07-05',1) as string)
from user_full where new_id is not null and old_id is not null;
"

dim_course_info_full="
with course as (
    select id,
           course_name,
           course_slogan,
           course_cover_url,
           subject_id,
           teacher,
           publisher_id,
           chapter_num,
           origin_price,
           reduce_amount,
           actual_price,
           course_introduce,
           create_time,
           update_time,
           deleted,
           dt
    from ods_course_info_full
    where dt = '$do_date'
),subject as(
    select id,
           subject_name,
           category_id
    from ods_base_subject_info_full
    where dt = '$do_date'
),category as (
    select  id,
            category_name
    from ods_base_category_info_full
    where dt = '$do_date'
)
insert overwrite table dim_course_info_full partition (dt='2022-07-05')
select
    course.`id`         ,
    `course_name`         ,
    `course_slogan`       ,
    `course_cover_url`  ,
    `subject_id`  ,
    `subject_name` ,
    `category_id` ,
    `category_name` ,
    `teacher`  ,
    `publisher_id`  ,
    `chapter_num`  ,
    `origin_price` ,
    `reduce_amount`  ,
    `actual_price`  ,
    `course_introduce`  ,
    `create_time`   ,
    `update_time`  ,
    `deleted`
from course
left join subject
on subject.id = subject_id
left join category
on category.id = subject.category_id;
"

dim_chapter_info_full ="with ci as (
    select
            id,
            chapter_name,
            course_id,
            video_id,
            create_time,
            update_time
    from ods_chapter_info_full
    where dt = '2022-07-05'
),cp as (
    select
           data.user_id,
           data.position_sec,
           data.course_id
    from ods_user_chapter_process_inc
    where dt = '2022-07-05'
),com_info as (
    select
           data.comment_txt,
           data.course_id
    from ods_comment_info_inc
    where dt = '2022-07-05'
),vf as (
    select
           video_name,
           during_sec,
           video_status,
           video_size,
           video_url,
           version_id,
           chapter_id
    from ods_video_info_full
    where dt = '2022-07-05')

    insert overwrite table dim_chapter_info_full partition (dt = '2022-07-05' )

select
       id,
       chapter_name,
       ci.course_id,
       video_id,
       create_time,
       update_time,
       chapter_id,
       user_id,
       position_sec,
       comment_txt,
       video_name,
       during_sec,
       video_status,
       video_size,
       video_url,
       version_id
from  ci left join cp on ci.course_id = cp.course_id
left join  com_info on ci.course_id = com_info.course_id
left join vf on ci.id = vf.chapter_id;"


case $2 in
"dim_user_zip")
	hive --database gmall -e "$dim_user_zip"
	;;
"dim_course_info_full")
	hive --database gmall -e "$dim_course_info_full"
	;;
"dim_chapter_info_full")
	hive --database gmall -e "$dim_chapter_info_full"
	;;
"all")
	hive --database gmall -e "$dim_user_zip$dim_course_info_full$dim_chapter_info_full"
	;;
esac